Solution 2: Create a Common Super-Table

In object-oriented polymorphism, two subtypes can be referenced similarly because they implicitly share a common supertype. In SQL, the Polymorphic Associations antipattern leaves out that crucial entity: the common supertype.

Creating a common super-table#

We can fix the mentioned issue by creating a base table that all of our parent tables are extended from (see Class Table Inheritance). We can add the foreign key in the child Comments table to reference the base table. We don’t need an issue_type column.

We can note that the primary keys of Bugs and FeatureRequests are also foreign keys. They reference the surrogate key value generated in the Issues table instead of generating a new value for themselves.

This solution is illustrated in the Entity-Relationship Diagram below.

Retrieving the referenced bug or feature request#

We can retrieve the referenced bug or feature request with a relatively simple query, given a specific comment. We don’t have to include the Issues table in that query at all unless we also define attribute columns in that table. Moreover, since the primary key values of the Bugs table and its ancestor Issues table are the same, we can join Bugs directly to Comments. We can join two tables even if there is no foreign key constraint linking them directly, as long as we use columns that represent comparable information in our database.

Retrieving comments of given a specific bug#

If we’re given a specific bug, we can retrieve its comments just as easily.

The point is that if we use an ancestor table like Issues, we can rely on the enforcement of our database’s data integrity by foreign keys.